﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using HanhuaMicroCredit.Admin.Extend;

using Library.Common;
using Library.Bll;

namespace HanhuaMicroCredit.Admin.Controllers
{
    public class OrderController : UserAuthenController
    {
        //
        // GET: /Order/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult test()
        {
            return View();
        }

        //
        // GET: /Order/Details/5

        public ActionResult Details(int id)
        {
            return View();
        }

        public ActionResult List()
        {
            //string table = "Order_Detail_Corporation";
            //string columns = "pid as order_id, corporation_name, contact_phone, business_type, annual_sales, loan_amount, mail, createdate";
            //List<Dictionary<string, object>> ls = CommonBLL.GetList(table, columns, "pid desc", "isValid=1" );
            return View("~/Views/Order/List.aspx");
        }

        #region Excel Export
        public ActionResult Export_3(string orderId, string handlerId, int status, string dateFrom, string dateTo)
        {
            string table = @"Order_Detail_Personal o left join Sys_IBUsers u on o.UpdateUserId=u.id
                        LEFT JOIN Base_District bd1 ON bd1.id = o.address_province
                        LEFT JOIN Base_District bd2 ON bd2.id = o.address_city
                        LEFT JOIN Base_District bd3 ON bd3.id = o.address_district
                        LEFT JOIN Sys_IBDD si ON si.Id = o.business_type
                        LEFT JOIN Sys_IBDD si2 ON si2.Id = o.loan_use";
            string columns = @"o.pid as 订单号, o.person_name as 姓名
                    , bd1.name + ' ' + bd2.name + ' ' + bd3.name + ' ' + o.address_remark as [经营地址]
                    , si.name as [单位类型]
                    , cast(o.monthly_income as varchar) + '元' as [税前月收入]
                    , cast(o.loan_amount as varchar) + '万' as 贷款金额
                    , cast(o.loan_period as varchar) + '月' as 贷款期限
                    , si2.name as 贷款用途 
                    , cast(o.contact_phone as varchar) as 手机号码
                    , cast(o.contact_phone2 as varchar) as 备用号码
                    , o.mail as 邮箱地址
                    , o.createdate as 申请时间, case o.status when 0 then '未处理' when 1 then '已处理' end as 状态
                    , u.username as 处理人, o.UpdateDate as 处理时间, o.Order_Remark as 需求描述, '' as [信贷经理回复]";
            string condition = "o.isValid=1";
            if (!string.IsNullOrEmpty(orderId))
                condition += string.Format(" and o.pid={0}", orderId);
            if (!string.IsNullOrEmpty(handlerId) && handlerId != "0")
                condition += string.Format(" and o.updateUserId={0}", handlerId);
            if (status != 9)
                condition += string.Format(" and o.status={0}", status);
            if (!string.IsNullOrEmpty(dateFrom))
                condition += string.Format(" and o.createdate>='{0}'", dateFrom);
            if (!string.IsNullOrEmpty(dateTo))
                condition += string.Format(" and o.createdate<='{0}'", dateTo + " 23:59:59");
            List<Dictionary<string, object>> ls = CommonBLL.GetList(table, columns, "o.pid desc", condition);
            return new ExcelResult(ls);
        }

        public ActionResult Export_1(string typ, string orderId, string handlerId, int status, string dateFrom, string dateTo)
        {
            string table = @"Order_Detail_Corporation o left join Sys_IBUsers u on o.UpdateUserId=u.id
                        LEFT JOIN Base_District bd1 ON bd1.id = o.address_province
                        LEFT JOIN Base_District bd2 ON bd2.id = o.address_city
                        LEFT JOIN Base_District bd3 ON bd3.id = o.address_district
                        LEFT JOIN Sys_IBDD si ON si.Id = o.business_type
                        LEFT JOIN Sys_IBDD si2 ON si2.Id = o.loan_use";
            string columns = @"o.pid as 订单号, o.corporation_name as [公司名称] 
                    , bd1.name + ' ' + bd2.name + ' ' + bd3.name + ' ' + o.address_remark as [经营地址]
                    , si.name as [从事行业]
                    , cast(o.annual_sales as varchar) + '万' as 年销售额
                    , cast(o.company_age AS VARCHAR) + '年' as 经营年限
                    , cast(o.loan_amount as varchar) + '万' as 贷款金额
                    , cast(o.loan_period as varchar) + '月' as 贷款期限
                    , si2.name as 贷款用途
                    , o.contact_phone as 手机号码
                    , o.createdate as 申请时间, case o.status when 0 then '未处理' when 1 then '已处理' end as 状态
                    , u.username as 处理人, o.UpdateDate as 处理时间, o.Order_Remark as 需求描述, '' as [信贷经理回复]";
            string condition = "o.isValid=1";
            if (!string.IsNullOrEmpty(typ))
                condition += string.Format(" and o.typ={0}", typ);
            if (!string.IsNullOrEmpty(orderId))
                condition += string.Format(" and o.pid={0}", orderId);
            if (!string.IsNullOrEmpty(handlerId) && handlerId != "0")
                condition += string.Format(" and o.updateUserId={0}", handlerId);
            if (status != 9)
                condition += string.Format(" and o.status={0}", status);
            if (!string.IsNullOrEmpty(dateFrom))
                condition += string.Format(" and o.createdate>='{0}'", dateFrom);
            if (!string.IsNullOrEmpty(dateTo))
                condition += string.Format(" and o.createdate<='{0}'", dateTo + " 23:59:59");
            List<Dictionary<string, object>> ls = CommonBLL.GetList(table, columns, "o.pid desc", condition);
            return new ExcelResult(ls);
        }

        public ActionResult Export_2(string typ, string orderId, string handlerId, int status, string dateFrom, string dateTo)
        {
            string table = @"Order_Detail_Corporation o left join Sys_IBUsers u on o.UpdateUserId=u.id
                        LEFT JOIN Base_District bd1 ON bd1.id = o.address_province
                        LEFT JOIN Base_District bd2 ON bd2.id = o.address_city
                        LEFT JOIN Base_District bd3 ON bd3.id = o.address_district
                        LEFT JOIN Sys_IBDD si ON si.Id = o.business_type
                        LEFT JOIN Sys_IBDD si2 ON si2.Id = o.loan_use";
            string columns = @"o.pid as 订单号, o.corporation_owner as [公司所有者]
                    , bd1.name + ' ' + bd2.name + ' ' + bd3.name + ' ' + o.address_remark as [经营地址]
                    , si.name as [从事行业]
                    , cast(o.annual_sales as varchar) + '万' as 年销售额
                    , cast(o.company_age AS VARCHAR) + '年' as 经营年限
                    , cast(o.loan_amount as varchar) + '万' as 贷款金额
                    , cast(o.loan_period as varchar) + '月' as 贷款期限
                    , si2.name as 贷款用途
                    , o.contact_phone as 手机号码
                    , o.createdate as 申请时间, case o.status when 0 then '未处理' when 1 then '已处理' end as 状态
                    , u.username as 处理人, o.UpdateDate as 处理时间, o.Order_Remark as 需求描述, '' as [信贷经理回复]";
            
            string condition = "o.isValid=1";
            if (!string.IsNullOrEmpty(typ))
                condition += string.Format(" and o.typ={0}",typ);
            if (!string.IsNullOrEmpty(orderId))
                condition += string.Format(" and o.pid={0}", orderId);
            if (!string.IsNullOrEmpty(handlerId) && handlerId != "0")
                condition += string.Format(" and o.updateUserId={0}", handlerId);
            if (status != 9)
                condition += string.Format(" and o.status={0}", status);
            if (!string.IsNullOrEmpty(dateFrom))
                condition += string.Format(" and o.createdate>='{0}'", dateFrom);
            if (!string.IsNullOrEmpty(dateTo))
                condition += string.Format(" and o.createdate<='{0}'", dateTo + " 23:59:59");
            List<Dictionary<string, object>> ls = CommonBLL.GetList(table, columns, "o.pid desc", condition);
            return new ExcelResult(ls);
        }
        #endregion

        public ActionResult GetListCorporation(string typ, string orderId, string handlerId, int status, string dateFrom, string dateTo, int pageindex, int pagesize)
        {
            string colName = "o.corporation_name as corporation_name";
            if (typ == "2")
                colName = "o.corporation_owner as corporation_name";

            string table = "Order_Detail_Corporation o left join Sys_IBUsers u on o.UpdateUserId=u.id";
            string columns = @"o.id, o.pid as order_id, " + colName + @", o.contact_phone, o.business_type, o.annual_sales, o.loan_amount, o.mail
                                , o.createdate, case o.status when 0 then '未处理' when 1 then '已处理' end as status
                                , u.username as UpdateUserName, u.realname as UpdateRealName, o.UpdateDate";
            string condition = "o.isValid=1";
            if (!string.IsNullOrEmpty(typ))
                condition += string.Format(" and o.typ={0}", typ);
            if (!string.IsNullOrEmpty(orderId))
                condition += string.Format(" and o.pid={0}", orderId);
            if (!string.IsNullOrEmpty(handlerId) && handlerId != "0")
                condition += string.Format(" and o.updateUserId={0}", handlerId);
            if (status != 9)
                condition += string.Format(" and o.status={0}", status);
            if (!string.IsNullOrEmpty(dateFrom))
                condition += string.Format(" and o.createdate>='{0}'", dateFrom);
            if (!string.IsNullOrEmpty(dateTo))
                condition += string.Format(" and o.createdate<='{0}'", dateTo + " 23:59:59");
            PagedInfo p = new PagedInfo(pageindex, pagesize);
            PagedResult<DataSet> pR = CommonBLL.GetPagedList(table, columns, "o.pid desc", condition, p);

            return PartialView("~/Views/Order/Shared/List_Corporation.ascx", pR);
        }

        public ActionResult GetListPersonal(string typ, string orderId, string handlerId, int status, string dateFrom, string dateTo, int pageindex, int pagesize)
        {
            string table = "Order_Detail_Personal o left join Sys_IBUsers u on o.UpdateUserId=u.id";
            string columns = @"o.id, o.pid as order_id, o.person_name, o.contact_phone, o.business_type, o.loan_amount, o.mail
                            , o.createdate, case o.status when 0 then '未处理' when 1 then '已处理' end as status, o.UpdateUserId 
                            , u.username as UpdateUserName, u.realname as UpdateRealName, o.UpdateDate";
            string condition = "o.isValid=1";
            if (!string.IsNullOrEmpty(orderId))
                condition += string.Format(" and o.pid={0}", orderId);
            if (!string.IsNullOrEmpty(handlerId) && handlerId != "0")
                condition += string.Format(" and o.updateUserId={0}", handlerId);
            if (status != 9)
                condition += string.Format(" and o.status={0}", status);
            if (!string.IsNullOrEmpty(dateFrom))
                condition += string.Format(" and o.createdate>='{0}'", dateFrom);
            if (!string.IsNullOrEmpty(dateTo))
                condition += string.Format(" and o.createdate<='{0}'", dateTo + " 23:59:59");
            PagedInfo p = new PagedInfo(pageindex, pagesize);
            PagedResult<DataSet> pR = CommonBLL.GetPagedList(table, columns, "o.pid desc", condition, p);

            return PartialView("~/Views/Order/Shared/List_Personal.ascx", pR);
        }

        public ActionResult GetDetailCorporation(int id)
        {
            Dictionary<string, object> d = GetDetail_Corporation(id);
            return PartialView("~/Views/Order/Shared/Detail_Corporation.ascx", d);
        }

        public ActionResult GetDetailPersonal(int id)
        {
            Dictionary<string, object> d = GetDetail_Personal(id);
            return PartialView("~/Views/Order/Shared/Detail_Personal.ascx", d);
        }

        public ActionResult Detail_Corporation(int id)
        {
            Dictionary<string, object> d = GetDetail_Corporation(id);
            return View(d);
        }

        public ActionResult Detail_Personal(int id)
        {
            Dictionary<string, object> d = GetDetail_Personal(id);
            return View(d);
        }

        public Dictionary<string, object> GetDetail_Corporation(int id)
        {
            string table = @"Order_Detail_Corporation odc 
                                left join sys_ibdd i1 on odc.business_type=i1.id
                                left join sys_ibdd i2 on odc.loan_use=i2.id
                                left join base_district d1 on odc.address_province=d1.id
                                left join base_district d2 on odc.address_city=d2.id
                                left join base_district d3 on odc.address_district=d3.id";
            string columns = @"odc.id, odc.pid as order_id, odc.typ, odc.corporation_name,odc.corporation_owner, odc.contact_phone, odc.contact_phone2
                            , odc.company_age , i1.name as business_type , odc.annual_sales , odc.loan_amount, odc.loan_period, i2.name as loan_use
                            , odc.mail, d1.name as address_province, d2.name as address_city, d3.name as address_district, odc.address_remark
                            , odc.order_remark, odc.createdate";
            string condition = string.Format("odc.pid = {0}", id);
            Dictionary<string, object> d = CommonBLL.GetModel(table, columns, condition);
            return d;
        }

        public Dictionary<string, object> GetDetail_Personal(int id)
        {
            string table = @"Order_Detail_Personal odp 
                                left join sys_ibdd i1 on odp.business_type=i1.id
                                left join sys_ibdd i2 on odp.loan_use=i2.id
                                left join base_district d1 on odp.address_province=d1.id
                                left join base_district d2 on odp.address_city=d2.id
                                left join base_district d3 on odp.address_district=d3.id";
            string columns = @"odp.id, odp.pid as order_id, odp.person_name, odp.contact_phone, odp.contact_phone2, odp.mail
                            , i1.name as business_type,  i2.name as loan_use, odp.loan_amount, odp.loan_period, odp.monthly_income
                            , d1.name as address_province, d2.name as address_city, d3.name as address_district, odp.address_remark
                            , odp.order_remark, odp.createdate";
            string condition = string.Format("odp.pid = {0}", id);
            Dictionary<string, object> d = CommonBLL.GetModel(table, columns, condition);
            return d;
        }
        [HttpPost]
        public ActionResult OrderRemarkSave(FormCollection fc)
        {
            try
            {
                Dictionary<string, object> d = new Dictionary<string, object>();
                d["order_remark"] = fc["t0_order_remark"];
                d["status"] = 1;
                CommonBLL.Update("Order_Detail_" + fc["order_typ"], d, string.Format("pid={0}", fc["hOrderId"].ToString()));
                InsLogBll.InsertLog("Order_Detail_", "OrderRemarkSave", "添加内容:" + d["order_remark"]);
                return Content("{\"statusCode\":\"200\",\"message\":\"保存成功\",\"navTabId\":\"navTab\",\"forwardConfirm\":\"success\",\"callbackType\":\"closeCurrent\"}");
            }
            catch (Exception e)
            {
                return Content(JUIResult.ResultMsg(e.Message));
            }
        }

        public ActionResult Import()
        {
            return View();
        }
        [HttpPost]
        public ActionResult ImportSave(FormCollection fc)
        {
            //string filePath = fc["file_loanInfo"].ToString();
            //TransferData(filePath, "贷款信息", System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ToString());
            TransferData4XML(System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnectionString"].ToString());
            return View("~/Views/Order/Import.aspx");
        }

        //
        // GET: /Order/Create

        public ActionResult Create()
        {
            return View();
        } 

        //
        // POST: /Order/Create

        [HttpPost]
        public ActionResult Create(FormCollection collection)
        {
            try
            {
                // TODO: Add insert logic here

                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
        
        //
        // GET: /Order/Edit/5
 
        public ActionResult Edit(int id)
        {
            return View();
        }

        //
        // POST: /Order/Edit/5

        [HttpPost]
        public ActionResult Edit(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add update logic here
 
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        //
        // GET: /Order/Delete/5
 
        public ActionResult Delete(int id)
        {
            return View();
        }

        //
        // POST: /Order/Delete/5

        [HttpPost]
        public ActionResult Delete(int id, FormCollection collection)
        {
            try
            {
                // TODO: Add delete logic here
 
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        #region Common method
        public void TransferData4Excel(string excelFile, string sheetName, string connectionString)
        {
            excelFile = @"F:\快盘\WorkSpace\07.瀚华通\0125\客户业务信息.xlsx";
            DataSet ds = new DataSet();
            try
            {
                //获取全部数据

                string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFile + "';Extended Properties='Excel 8.0;HDR=YES;'";
                DataTable dt = new DataTable();
                if (excelFile.ToLower().IndexOf(".xlsx") > 0)
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + excelFile + "';Extended Properties='Excel 12.0;HDR=YES'";
                }
                if (excelFile.ToLower().IndexOf(".xls") > 0 && excelFile.EndsWith("xls"))
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + excelFile + "';Extended Properties='Excel 8.0;HDR=YES;'";
                }
                OleDbConnection conn = new OleDbConnection(strConn);

                conn.Open();
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                strExcel = string.Format("select * from [{0}$]", sheetName);
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(ds, sheetName);

                //如果目标表不存在则创建
                string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
                foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
                {
                    strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
                }
                strSql = strSql.Trim(',') + ")";

                using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
                {
                    sqlconn.Open();
                    System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                    command.CommandText = strSql;
                    command.ExecuteNonQuery();
                    sqlconn.Close();
                }
                //用bcp导入数据
                using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
                {
                    //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                    bcp.BatchSize = 100;//每次传输的行数
                    bcp.NotifyAfter = 100;//进度提示的行数
                    bcp.DestinationTableName = sheetName;//目标表
                    bcp.WriteToServer(ds.Tables[0]);
                }
            }
            catch (Exception ex)
            {
                //System.Windows.Forms.MessageBox.Show(ex.Message);
            }

        }

        public void TransferData4XML(string connectionString)
        {
            DataSet ds = new DataSet();
            ds.ReadXml(Request.PhysicalApplicationPath + @"\UploadData\201302\01.xml", XmlReadMode.Auto);

            //如果目标表不存在则创建
            string strSql = string.Format("if object_id('{0}') is null create table {0}(", "tmp_Loan_RepaymentRecord");
            foreach (System.Data.DataColumn c in ds.Tables[2].Columns)
            {
                strSql += string.Format("[{0}] nvarchar(500),", c.ColumnName);
            }
            strSql = strSql.Trim(',') + ")";

            using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                sqlconn.Open();
                System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
                command.CommandText = strSql;
                command.ExecuteNonQuery();
                sqlconn.Close();
            }

            //用bcp导入数据
            using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
            {
                //bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
                bcp.BatchSize = 100;//每次传输的行数
                bcp.NotifyAfter = 100;//进度提示的行数
                try
                {
                    bcp.DestinationTableName = "tmp_Loan_InfoList";//目标表
                    bcp.WriteToServer(ds.Tables[0]);
                }
                catch (SqlException se)
                { 
                    //此文件里含有已传过的数据，请不要重复上传
                }
                try
                {
                    bcp.DestinationTableName = "tmp_Loan_RepaymentScheme";//目标表
                    bcp.WriteToServer(ds.Tables[1]);
                }
                catch (SqlException se)
                {
                    //此文件里含有已传过的数据，请不要重复上传
                }
                try
                {
                bcp.DestinationTableName = "tmp_Loan_RepaymentRecord";//目标表
                bcp.WriteToServer(ds.Tables[2]);
                }
                catch (SqlException se)
                {
                    //此文件里含有已传过的数据，请不要重复上传
                }

            }
        }
        ////进度显示
        //void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
        //{
        //    this.Text = e.RowsCopied.ToString();
        //    this.Update();
        //}

        #endregion 
    }
}
